<?php
/*
  This file is part of Open-Doors.

  Open-Doors is free software: you can redistribute it and/or modify
  it under the terms of the GNU General Public License as published by
  the Free Software Foundation, either version 3 of the License, or
  (at your option) any later version.
  
  Open-Doors is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU General Public License for more details.
  
  You should have received a copy of the GNU General Public License
  along with Open-Doors.  If not, see <http://www.gnu.org/licenses/>.

  Authors: Rune Thorbek & Araz B. Makoo
*/

include 'db_connect.php';
include 'functions.php';
//include 'db_functions.php';

sec_session_start();
if(login_check($mysqli) != true) {
  die('{"success": false, "msg": "Du er logget ud. Log venligst ind igen."}');
}

function remove_empty_groups($mysqli) {
  $sql = "DELETE FROM groups WHERE uid IN (SELECT uid FROM (SELECT groups.uid FROM `".DATABASE."`.`groups` LEFT JOIN `".DATABASE."`.`collectors` ON groups.uid=collectors.group_uid WHERE collectors.group_uid IS NULL) as foo)";
  $result = $mysqli->query($sql);
}


/* 
   CSV format:

   [0]  => Røde Kors Nummer
   [1]  => Tidspunkt
   [2]  => Afbud
   [3]  => Fornavn
   [4]  => Efternavn
   [5]  => Alder
   [6]  => Adresse
   [7]  => Postnr
   [8]  => By
   [9]  => Mobil
   [10] => Fastnet
   [11] => Email
   [12] => Særlig rute
   [13] => Ingen trapper
   [14] => Samler ind med
   [15] => Bemærkning
   [16] => Ekstra indsamlingsbøsser
   [17] => Kilde
   [18] => Kode
   [19] => Organisation
   [20] => Tilmeldingsnummer
   [21] => Lokalafdeling
   [22] => Hørt om

 */
function convertData($data) {
  $prefix = iconv("UTF8", "ISO-8859-4", "København - ");
  $length = strlen($prefix);
  $venue  = $data[21];
  if (strlen($venue) > $length and substr($venue, 0, $length) == $prefix)
    $data[21] = substr($venue, $length);

  return array(
	       "number" => $data[0],
	       "cancel" => $data[2],
	       "first_name" => trim($data[3]),
	       "last_name" => trim($data[4]),
	       "age" => trim($data[5]),
	       "address" => trim($data[6]),
	       "post_no" => trim($data[7]),
	       "city" => trim($data[8]),
	       "mobile_number" => $data[9],
	       "phone_number" => $data[10],
	       "email" => trim($data[11]),
	       "collects_with" => $data[14],
	       "comment" => $data[15],
	       "organisation" => trim($data[19]),
	       "venue" => trim($data[21])
	      );
}

function createSQLQuery($data, $venue_uid, $campaign_uid) {
  $sql = "INSERT INTO `collectors` (number, last_name, first_name, address, mobile_number, phone_number, email_address, venue_uid, campaign_uid) VALUES (".$data["number"].", '".$data["last_name"]."','".$data["first_name"]."','".$data["address"]."','".$data["mobile_number"]."','".$data["phone_number"]."','".$data["email"]."', ".$venue_uid.", ".$campaign_uid.")";
  return $sql;
}

function createGroupSQLQuery($data) {
  $sql = "INSERT INTO `".DATABASE."`.`groups` (`no_collection_boxes`, `returned_collection_boxes`, `email_wish`, `same_route`, `amount_coins`, `amount_bank_notes`, `amount_app`, `comment`, `collects_with_field`) VALUE (0,0,0,0,0,0,0,'".$data["comment"]."','".$data["collects_with"]."')";
  return $sql;
}

function createVenueSQLQuery($name, $code) {
  $sql = "INSERT INTO `".DATABASE."`.`venues` (`name`, `code`) VALUE ('".$name."', '".$code."')";
  return $sql;
}

function createCampaignSQLQuery($name, $code) {
  $sql = "INSERT INTO `".DATABASE."`.`campaigns` (`name`) VALUE ('".$name."')";
  return $sql;
}

function updateCollectorGroupSQLQuery($data, $group_uid) {
  $sql = "UPDATE `".DATABASE."`.`collectors` SET `group_uid`=".$group_uid." WHERE `number`=".$data["number"];
  return $sql;
}

function deleteSQLQuery($data) {
  $sql = "DELETE FROM `".DATABASE."`.`collectors` WHERE `number`=".$data["number"];
  return $sql;
}

/* Only allow csv files to be imported */
$allowedExts = array("csv");
$extension = end(explode(".", $_FILES["file"]["name"]));
if ( in_array($extension, $allowedExts))
  {
  if ($_FILES["file"]["error"] > 0)
    {
     echo '{"success": false, "msg": "Fejl: "' . $_FILES["file"]["error"] . '"}';	
    }
  else
    {
	/* Generate a temporary file (will be deleted automatically 
	   after script is done) */
       $csv_file = $_FILES["file"]["tmp_name"];

       $file = fopen($csv_file, "r");

       /* First we parse the and delete the collectors and groups (if
	  empty) */
       $start = false;


       /* Read line by line from the cvs file */
       while (($row = fgetcsv($file, 4096, ";")) !== FALSE) {
 	     /* To avoid first row of cvs file */
 	     if ($row[1] == "Tidspunkt") {
 	       $start = true;
 	     	continue;
 	     }
 	     if (!$start) {
 	       continue;
 	     }
 
 	     $data = convertData($row);
 
 	     // we need to delete group if this was the last member :-/
 	     /* First find the group uid */
 	     $sql = "SELECT `group_uid` as guid, (SELECT COUNT(uid) FROM `".DATABASE."`.`collectors` WHERE `group_uid`=guid) FROM `".DATABASE."`.`collectors` WHERE `number`=".$data["number"];

 	     $result = $mysqli->query($sql);
 	     if (!$result) {
 	        die("MySQL ERROR: ".$mysqli->error);	   
 	     }
 	     $row = $result->fetch_row();
 	     $group_uid = $row[0];
 	     $members   = $row[1];
 	     if ($members == 1) {
 	       $sql = "DELETE FROM `".DATABASE."`.`groups` WHERE `uid`=".$group_uid;
 	       $mysqli->query($sql);
 	     }
 	     
 	     $query = deleteSQLQuery($data);
 	     /* This will execute the SQL query */
 	     $mysqli->query($query);

       }
       
       fseek($file, 0);

       /* Then we parse the csv file again, and insert all members. We
	  cannot create the groups yet, because we need to know all
	  members to create groups. */
       $start = false;

       /* Read line by line from the cvs file */
       while (($row = fgetcsv($file, 1024, ";")) !== FALSE) {
       	     /* To avoid first row of cvs file */
 	     if ($row[1] == "Tidspunkt") {
 	       $start = true;
 	     	continue;
 	     }
 	     if (!$start) {
 	       continue;
 	     }
 
 	     $data = convertData($row);
 
 	     if ($data["cancel"] == "") {
	       /* we need to get the venue uid. */
	       /* First let's make sure it is defined */
	       $venue_uid = 0;
	       if ($data["venue"] != "") {
		 $sql = "SELECT uid FROM `".DATABASE."`.`venues` WHERE `name`='".$data["venue"]."'";
		 $result = $mysqli->query($sql);
		 if ($result->num_rows == 0) {
		   /* Then the venue does not exist, let's create it */
		   $query = createVenueSQLQuery($data["venue"], "");
		   $mysqli->query($query);
 	     
		   $venue_uid = mysqli_insert_id($mysqli);
		 } else {
		   $row = $result->fetch_row();
		   $venue_uid = $row[0];
		 }
	       }
	       /* we need to get the campaign uid. */
	       /* First let's make sure it is defined. */
	       $campaign_uid = 0;
	       if ($data["organisation"] != "") {
		 $sql = "SELECT uid FROM `".DATABASE."`.`campaigns` WHERE `name`='".$data["organisation"]."'";
		 $result = $mysqli->query($sql);
		 if ($result->num_rows == 0) {
		   /* Then the campaign does not exist, let's create it */
		   $query = createCampaignSQLQuery($data["organisation"]);
		   $mysqli->query($query);
 	     
		   $campaign_uid = mysqli_insert_id($mysqli);
		 } else {
		   $row = $result->fetch_row();
		   $campaign_uid = $row[0];
		 }
	       }

 	       $query = createSQLQuery($data, $venue_uid, $campaign_uid);
 	       /* This will execute the SQL query */
 	       $mysqli->query($query);

 	     }
       }


       fseek($file, 0);

       /* Finally we create all groups. */
       $start = false;

       /* Read line by line from the cvs file */
       while (($row = fgetcsv($file, 1024, ";")) !== FALSE) {
        	     /* To avoid first row of cvs file */
 	     if ($row[1] == "Tidspunkt") {
 	       $start = true;
 	     	continue;
 	     }
 	     if (!$start) {
 	       continue;
 	     }
 
 	     $data = convertData($row);
 
 	     if ($data["cancel"] == "") {
 
 	       /* First check if this collector is in another group */
 	       $collector_name = $data["first_name"]." ".$data["last_name"];
 	       $sql = "SELECT uid FROM `".DATABASE."`.`groups` WHERE `collects_with_field` LIKE '%".$collector_name."%'";
 	       $result = $mysqli->query($sql);
 	       if ($result->num_rows == 0) {
 		 $query = createGroupSQLQuery($data);
 		 $mysqli->query($query);
 	     
 		 $group_uid = mysqli_insert_id($mysqli);
 	       } else {
 		 $row = $result->fetch_row();
 		 $group_uid = $row[0];
 	       }
    
 	       $query = updateCollectorGroupSQLQuery($data, $group_uid);
 	       $mysqli->query($query);
 
 	     }
       }

       remove_empty_groups($mysqli);

       fclose($file);

       echo '{"success": true, "msg": "Filen er uploadet til databasen."}';
    }
  }
else
  {
     echo '{"success": false, "msg": "Forkert filformat. Vælg venligst en CSV fil."}';
     return;
  }
?>